package com.newedu.equipmgr.dao;

import com.newedu.equipmgr.entity.SysPermission;
import com.newedu.equipmgr.entity.SysPermissionMenuVO;
import com.newedu.equipmgr.util.MyDBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author jerry
 * @create 2020-06-10 17:28:37
 * @company 新开普电子股份有限公司
 */
public class SysPermissionDaoImpl implements SysPermissionDao {
    private QueryRunner queryRunner = new QueryRunner(MyDBUtils.getDataSource());
    @Override
    public boolean insert(SysPermission sysPermission) throws SQLException {
        // 对参数进行合法性校验
        if(sysPermission == null){
            throw new IllegalArgumentException("参数不正确 : " + sysPermission);
        }
        String sql = "insert into `equipmentmgr`.`t_permissions`\n" +
                "            (`id`,\n" +
                "             `permissionname`,\n" +
                "             `permissiontype`,\n" +
                "             `url`,\n" +
                "             `parent_id`,\n" +
                "             `sort`,\n" +
                "             `createuserid`,\n" +
                "             `updateuserid`)\n" +
                "values (null,\n" +
                "        ?,\n" +
                "        ?,\n" +
                "        ?,\n" +
                "        ?,\n" +
                "        ?,\n" +
                "        ?,\n" +
                "        ?);";

        Object[] params = new Object[]{sysPermission.getPermissionname(),
        sysPermission.getPermissiontype(),
        sysPermission.getUrl(),
        sysPermission.getParent_id(),
        sysPermission.getSort(),
        sysPermission.getCreateuserid(),
        sysPermission.getUpdateuserid()};
        int insert = queryRunner.update(sql, params);
        return insert == 1?true:false;
    }

    @Override
    public boolean update(SysPermission sysPermission) throws SQLException {
        // 对参数进行合法性校验
        if(sysPermission == null){
            throw new IllegalArgumentException("参数不正确 : " + sysPermission);
        }

        String sql = "UPDATE `equipmentmgr`.`t_permissions`\n" +
                "SET `id` = 'id',\n" +
                "  `permissionname` = ?,\n" +
                "  `permissiontype` = ?,\n" +
                "  `url` = ?,\n" +
                "  `parent_id` = ?,\n" +
                "  `sort` = ?,\n" +
                "  `updateuserid` = ?,\n" +
                "  `updatetime` = ?\n" +
                "WHERE `id` = ?;";
        Object[] params = new Object[]{sysPermission.getPermissionname(),
                sysPermission.getPermissiontype(),
                sysPermission.getUrl(),
                sysPermission.getParent_id(),
                sysPermission.getSort(),
                sysPermission.getCreateuserid(),
                sysPermission.getCreatetime(),
                sysPermission.getUpdateuserid(),
                sysPermission.getDel(),
                sysPermission.getId()};
        int update = queryRunner.update(sql, params);
        return update == 1?true:false;
    }

    @Override
    public boolean delete(int id) throws SQLException {
        // 对参数进行合法性校验
        if(id == 0){
            throw new IllegalArgumentException("参数不正确 : " + id);
        }

        String sql = "delete\n" +
                "from `equipmentmgr`.`t_permissions`\n" +
                "where `id` = ?;";
        int delete = queryRunner.update(sql, id);
        return delete == 1?true:false;
    }

    @Override
    public List<SysPermission> selectAll() throws SQLException {
        String sql = "select * from t_permissions";
        List<SysPermission> permissionInfoList = queryRunner.query(sql, new BeanListHandler<>(SysPermission.class));
        return permissionInfoList;
    }

    @Override
    public SysPermission selectOne(int id) throws SQLException {
        // 对参数进行合法性校验
        if(id == 0){
            throw new IllegalArgumentException("参数不正确 : " + 0);
        }
        String sql = "select * from t_permissions where id = ?";
        SysPermission permissionInfo = queryRunner.query(sql, new BeanHandler<>(SysPermission.class), id);
        return permissionInfo;
    }

    @Override
    public List<SysPermission> selectBy(String condition, int pageNum, int pageSize) throws SQLException {
        String sql = "select * from t_permissions ";
        // 对参数进行合法性校验
        if(condition != null && !condition.isEmpty()){
            sql += condition;
        }
        if(pageNum != 0){
            sql += " limit "+ pageSize*(pageNum-1) +" , "+ pageSize ;
        }

        System.out.println(sql);
        List<SysPermission> permissionInfoList = queryRunner.query(sql, new BeanListHandler<>(SysPermission.class));
        return permissionInfoList;
    }

    @Override
    public int countRows(String condition) throws SQLException {
        String sql = "select count(*) from t_permissions ";
        // 对参数进行合法性校验
        if(condition != null && !condition.isEmpty()){
            sql += condition;
        }
        Object query = queryRunner.query(sql, new ScalarHandler<>());
        return Integer.parseInt(query.toString());
    }

    @Override
    public List<SysPermissionMenuVO> queryPermissionListBy(String userid) throws SQLException {
        String sql = "SELECT a.`id` AS userid, a.`username`, b.`id` AS roleid, rolename, d.`id` AS permissionid,permissionname,permissiontype,menuno,url,parent_id,sort\n" +
                "FROM t_users AS a,t_roles AS b,t_users_roles c,t_permissions d,t_roles_permissions e\n" +
                "WHERE 1=1\n" +
                "AND c.`userid` = a.`id` AND c.`roleid`=b.`id` AND e.`roleid` = b.`id` AND d.`id` = e.`permissionid`\n" +
                "AND a.`id` = ?";
        List<SysPermissionMenuVO> permissionMenuVOList = queryRunner.query(sql, new BeanListHandler<>(SysPermissionMenuVO.class),userid);
        return permissionMenuVOList;
    }
}
